package cc.autoapi.pucong.liteflow.design.utils;

import cc.autoapi.pucong.liteflow.design.core.exception.DesignException;
import cc.autoapi.pucong.liteflow.design.mobal.DbType;
import cc.autoapi.pucong.liteflow.design.mobal.ExeRunSqlResultPage;
import cc.autoapi.pucong.liteflow.design.mobal.ExeSqlResult;
import cc.autoapi.pucong.liteflow.design.mobal.vo.BasePageVo;
import cc.autoapi.pucong.liteflow.design.mobal.vo.ResultFiledVo;
import cn.hutool.core.lang.Assert;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据库工具类
 *
 * @author zyb
 * @company www.jic.org
 * @create 2022/11/3 16:02
 */
public class DatabaseUtils {
    private static final Logger log = LoggerFactory.getLogger(DatabaseUtils.class);

    public static ExeRunSqlResultPage runSQLByPage(int pageNum, int pageSize, String sql, List<Object> params, Connection connection, Boolean isValueList) {
        long start = System.currentTimeMillis();
        ExeRunSqlResultPage runSQLResult = new ExeRunSqlResultPage();
        try {
            // 查询总数
            ExeSqlResult exeSqlResult = getTotal(params, sql, connection);
            BigDecimal total = exeSqlResult.getResultInteger();
            BasePageVo<Map<String, Object>> listIPage = new BasePageVo(pageSize, pageNum);
            ExeSqlResult execResult = null;
            if (total.compareTo(BigDecimal.ZERO) == 0) {
                execResult = new ExeSqlResult();
                execResult.setFiledList(new ArrayList<>());
                execResult.setResultList(new ArrayList<>());
                execResult.setValueList(new ArrayList<>());
                runSQLResult.setExecTime(0.00);
            } else {
                BasePageVo page = new BasePageVo<>(pageSize, pageNum);
                DbType dbType = getDbType(connection.getMetaData().getURL());
                String pageSql = buildPageSql(dbType, sql, page.offset(), page.getPageSize());
                execResult = execQuerySql(params, pageSql, connection, isValueList);
                // 查询分页数据
                listIPage.setRecords(execResult.getResultList());
                runSQLResult.setDataList(execResult.getValueList());
                runSQLResult.setFiledList(execResult.getFiledList());
                runSQLResult.setResultInteger(execResult.getResultInteger());
            }
            // 条数
            listIPage.setTotal(total.longValue());
            // 当前页数
            listIPage.setPageNum(pageNum);
            // 总页数
            listIPage.setPages(total.divide(new BigDecimal(pageSize), RoundingMode.UP).intValue());
            // 一页大小
            listIPage.setPageSize(pageSize);
            runSQLResult.setExecResultMapPage(listIPage);
            runSQLResult.setExecMsg("ok");
            runSQLResult.setExecStatus(1);
        } catch (Exception e) {
            log.error("调试失败：" + e);
            runSQLResult.setExecMsg(e.getMessage());
            runSQLResult.setExecStatus(0);
        }
        long end = System.currentTimeMillis();
        runSQLResult.setExecTime(new BigDecimal((double) (end - start) / 1000).setScale(2, RoundingMode.HALF_UP).doubleValue());
        return runSQLResult;
    }

    private static String buildPageSql(DbType dbType, String sql, long offset, Integer pageSize) {
        if (dbType == DbType.POSTGRE_SQL) {
            return buildPgPaginationSql(sql, offset, pageSize);
        } else if (dbType == DbType.H2) {
            return buildPgPaginationSql(sql, offset, pageSize);
        } else if (dbType == DbType.MYSQL) {
            return buildMysqlPaginationSql(sql, offset, pageSize);
        } else if (dbType == DbType.SQL_SERVER) {
            return buildSqlserviceBasePaginationSql(sql, offset, pageSize);
        } else if (dbType == DbType.SQL_SERVER2005) {
            return buildSqlservice2005PaginationSql(sql, offset, pageSize);
        } else if (dbType == DbType.ORACLE) {
            return buildOraclePaginationSql(sql, offset, pageSize);
        } else if (dbType == DbType.ORACLE_12C) {
            return build12CPaginationSql(sql, offset, pageSize);
        } else {
            throw new DesignException("暂不支持的类型：".concat(dbType.getDb()));
        }
    }

    private static String buildPgPaginationSql(String originalSql, long offset, long limit) {
        return String.format("%s limit %s offset %s", originalSql, limit, offset);
    }

    private static String buildOraclePaginationSql(String originalSql, long offset, long limit) {
        limit = offset >= 1L ? offset + limit : limit;
        return String.format("SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( %s) TMP WHERE ROWNUM <=%s) WHERE ROW_ID > %s", originalSql, limit, offset);
    }

    private static String build12CPaginationSql(String originalSql, long offset, long limit) {
        return String.format("%s OFFSET %s ROWS FETCH NEXT %s ROWS ONLY ", originalSql, offset, limit);
    }

    private static String buildMysqlPaginationSql(String originalSql, long offset, long limit) {
        return String.format("%s LIMIT %s,%s", originalSql, offset, limit);
    }

    private static String getOrderByPart(String sql) {
        String loweredString = sql.toLowerCase();
        int orderByIndex = loweredString.indexOf("order by");
        return orderByIndex != -1 ? sql.substring(orderByIndex) : "";
    }

    private static String buildSqlservice2005PaginationSql(String originalSql, long offset, long limit) {
        StringBuilder pagingBuilder = new StringBuilder();
        String orderby = getOrderByPart(originalSql);
        String distinctStr = "";
        String loweredString = originalSql.toLowerCase();
        String sqlPartString = originalSql;
        if (loweredString.trim().startsWith("select")) {
            int index = 6;
            if (loweredString.startsWith("select distinct")) {
                distinctStr = "DISTINCT ";
                index = 15;
            }

            sqlPartString = originalSql.substring(index);
        }

        pagingBuilder.append(sqlPartString);
        if (org.apache.commons.lang3.StringUtils.isBlank(orderby)) {
            orderby = "ORDER BY CURRENT_TIMESTAMP";
        }

        long firstParam = offset + 1L;
        long secondParam = offset + limit;
        String sql = "WITH selectTemp AS (SELECT " + distinctStr + "TOP 100 PERCENT  ROW_NUMBER() OVER (" + orderby + ") as __row_number__, " + pagingBuilder + ") SELECT * FROM selectTemp WHERE __row_number__ BETWEEN " + firstParam + " AND " + secondParam + " ORDER BY __row_number__";
        return sql;
    }

    public static String buildSqlserviceBasePaginationSql(String originalSql, long offset, long limit) {
        return String.format("%s OFFSET %s ROWS FETCH NEXT %s ROWS ONLY ", originalSql, offset, limit);
    }


    private static String getCountSql(String lowerCaseString) {
        String querySql = "";
        if (lowerCaseString.endsWith(";")) {
            querySql = strPlus(" select count(1) as countNum from (", lowerCaseString.substring(0, lowerCaseString.length() - 1), ") as m");
        } else {
            querySql = strPlus(" select count(1) as countNum from (", lowerCaseString, ") as m");
        }
        return querySql;
    }

    private static String strPlus(String... strs) {
        StringBuffer stringBuffer = new StringBuffer();

        for (int i = 0; i < strs.length; ++i) {
            stringBuffer.append(strs[i]);
        }

        return stringBuffer.toString();
    }

    public static ExeSqlResult getTotal(List<Object> params, String sql, Connection connection) throws SQLException {
        sql = getCountSql(sql);
        ExeSqlResult exeSqlResult = execQuerySql(params, sql, connection, true);
        List<List<Object>> valueList = exeSqlResult.getValueList();
        Object count = valueList.get(0).get(0);
        exeSqlResult.setResultInteger(new BigDecimal(count.toString()));
        return exeSqlResult;
    }

    public static ExeSqlResult execQuerySql(List<Object> params, String sql, Connection connection, Boolean isValueList) throws SQLException {
        ExeSqlResult exeSqlResult = new ExeSqlResult();
        PreparedStatement pst = null;
        ResultSet rst = null;
        DbType dbType = getDbType(connection.getMetaData().getURL());
        // 执行SQL获得结果集
        try {
            log.info("执行sql: " + sql);
            // 调用SQL
            pst = connection.prepareStatement(sql);
            // 参数赋值
            if (params != null) {
                for (int i = 0; i < params.size(); i++) {
                    pst.setObject(i + 1, params.get(i));
                }
            }
            // 执行
            rst = pst.executeQuery();
            // 创建ResultSetMetaData对象
            // 结果集列数
            int columnCount = 0;
            ResultSetMetaData rsmd = rst.getMetaData();
            // 获得结果集列数
            columnCount = rsmd.getColumnCount();
            // 创建List
            List<ResultFiledVo> filedList = new ArrayList<>();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = rsmd.getColumnName(i);
                if (DbType.SQL_SERVER2005.equals(dbType)) {
                    if ("__row_number__".equals(columnName)) {
                        continue;
                    }
                } else if (!DbType.ORACLE_12C.equals(dbType)) {
                    if ("ROW_ID".equals(columnName)) {
                        continue;
                    }
                }
                ResultFiledVo resultFiledVo = new ResultFiledVo();
                resultFiledVo.setFiledName(columnName.toLowerCase());
                resultFiledVo.setFiledType(rsmd.getColumnTypeName(i));
                filedList.add(resultFiledVo);
            }

            if (isValueList) {
                /*************** 获取数据 ****************/
                List<List<Object>> list2 = new ArrayList<>();
                List<Object> l = null;
                while (rst.next()) {
                    l = new ArrayList<>();
                    for (int i = 0; i < filedList.size(); i++) {
                        l.add(rst.getObject(filedList.get(i).getFiledName()));
                    }
                    list2.add(l);
                }
                exeSqlResult.setValueList(list2);
            } else {
                List<Map<String, Object>> list = new ArrayList<>();
                // 将ResultSet的结果保存到List中
                while (rst.next()) {
                    Map<String, Object> map = new HashMap<>();
                    for (int i = 0; i < filedList.size(); i++) {
                        map.put(filedList.get(i).getFiledName().toLowerCase(), rst.getString(filedList.get(i).getFiledName()));
                    }
                    //每一个map代表一条记录，把所有记录存在list中
                    list.add(map);
                }
                exeSqlResult.setResultList(list);
            }
            exeSqlResult.setFiledList(filedList);
        } catch (SQLException e) {
            log.error("执行sql异常", e);
            throw new DesignException("执行sql异常" + e.getMessage());
        } finally {
            LiteFlowJdbcUtil.closeResultSet(rst);
            LiteFlowJdbcUtil.closePreparedStatement(pst);
        }
        return exeSqlResult;
    }

    public static ExeSqlResult execSql(Connection con, String sql, List<Object> params, boolean isValue) {
        ExeSqlResult exeSqlResult = new ExeSqlResult();
        exeSqlResult.setResultInteger(new BigDecimal(0));
        long start = System.currentTimeMillis();
        long end;
        PreparedStatement pst = null;
        try {
            pst = con.prepareStatement(sql);
            // 参数赋值
            if (params != null) {
                for (int i = 0; i < params.size(); i++) {
                    pst.setObject(i + 1, params.get(i));
                }
            }
            boolean execute = pst.execute();
            if (execute) {
                ResultSet resultSet = pst.getResultSet();
                ExeSqlResult result = getResult(resultSet, isValue);
                exeSqlResult.setFiledList(result.getFiledList());
                int size = result.getResultList().size();
                // 获取自增长id
                ResultSet generatedKeys = pst.getGeneratedKeys();
                while (generatedKeys.next()) {
                    long aLong = generatedKeys.getLong(1);
                    result.setGeneratedKey(aLong);
                }
            } else {
                int updateCount = pst.getUpdateCount();
                exeSqlResult.setResultInteger(new BigDecimal(updateCount));
            }
            end = System.currentTimeMillis();
        } catch (Exception e) {
            log.error("[execSql Exception] --> "
                    + "the exception message is:" + e.getMessage());
            end = System.currentTimeMillis();
        } finally {
            LiteFlowJdbcUtil.closePreparedStatement(pst);
        }
        exeSqlResult.setExecTime(BigDecimal.valueOf((double) (end - start) / 1000).setScale(2, RoundingMode.HALF_UP).doubleValue());
        return exeSqlResult;
    }

    public static ExeSqlResult getResult(ResultSet rst, boolean isValue) {
        ExeSqlResult exeSqlResult = new ExeSqlResult();
        // 执行SQL获得结果集
        try {
            // 创建ResultSetMetaData对象
            // 结果集列数
            int columnCount = 0;
            ResultSetMetaData rsmd = rst.getMetaData();
            // 获得结果集列数
            columnCount = rsmd.getColumnCount();
            // 创建List
            List<ResultFiledVo> filedList = new ArrayList<>();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = rsmd.getColumnName(i);
                ResultFiledVo resultFiledVo = new ResultFiledVo();
                resultFiledVo.setFiledName(columnName);
                resultFiledVo.setFiledType(rsmd.getColumnTypeName(i));
                filedList.add(resultFiledVo);
            }
            if (isValue) {
                List<List<Object>> list2 = new ArrayList<>();
                // 将ResultSet的结果保存到List中
                while (rst.next()) {
                    List<Object> valueList = new ArrayList<>();
                    for (int i = 0; i < filedList.size(); i++) {
                        valueList.add(rst.getObject(i + 1));
                    }
                    //每一个list代表一条记录，把所有记录存在list中
                    list2.add(valueList);
                }
                exeSqlResult.setValueList(list2);
            } else {
                List<Map<String, Object>> list = new ArrayList<>();
                // 将ResultSet的结果保存到List中
                while (rst.next()) {
                    Map<String, Object> map = new HashMap<>();
                    for (int i = 0; i < filedList.size(); i++) {
                        map.put(filedList.get(i).getFiledName(), rst.getString(i + 1));
                    }
                    //每一个map代表一条记录，把所有记录存在list中
                    list.add(map);
                }
                exeSqlResult.setResultList(list);
            }
            exeSqlResult.setFiledList(filedList);
        } catch (SQLException e) {
            log.error("执行sql异常", e);
            throw new DesignException("执行sql异常" + e.getMessage());
        } finally {
            LiteFlowJdbcUtil.closeResultSet(rst);
        }
        return exeSqlResult;
    }

    public static DbType getDbType(String jdbcUrl) {
        Assert.isFalse(StringUtils.isBlank(jdbcUrl), "Error: The jdbcUrl is Null, Cannot read database type");
        String url = jdbcUrl.toLowerCase();
        if (url.contains(":mysql:") || url.contains(":cobar:")) {
            return DbType.MYSQL;
        } else if (url.contains(":mariadb:")) {
            return DbType.MARIADB;
        } else if (url.contains(":oracle:")) {
            return DbType.ORACLE;
        } else if (url.contains(":sqlserver:") || url.contains(":microsoft:")) {
            return DbType.SQL_SERVER2005;
        } else if (url.contains(":sqlserver2012:")) {
            return DbType.SQL_SERVER;
        } else if (url.contains(":postgresql:")) {
            return DbType.POSTGRE_SQL;
        } else if (url.contains(":hsqldb:")) {
            return DbType.HSQL;
        } else if (url.contains(":db2:")) {
            return DbType.DB2;
        } else if (url.contains(":sqlite:")) {
            return DbType.SQLITE;
        } else if (url.contains(":h2:")) {
            return DbType.H2;
        } else if (url.contains(":dm:")) {
            return DbType.DM;
        } else if (url.contains(":xugu:")) {
            return DbType.XU_GU;
        } else if (url.contains(":kingbase:") || url.contains(":kingbase8:")) {
            return DbType.KINGBASE_ES;
        } else if (url.contains(":phoenix:")) {
            return DbType.PHOENIX;
        } else if (jdbcUrl.contains(":zenith:")) {
            return DbType.GAUSS;
        } else {
            log.warn("The jdbcUrl is " + jdbcUrl + ", Mybatis Plus Cannot Read Database type or The Database's Not Supported!");
            return DbType.OTHER;
        }
    }
}
